/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package helper;

import dbconnector.DBDriver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class course 
{
    static PreparedStatement p,pstmt;
    static ResultSet rs;
    public static AO.Course getCourseBean(String courseid)
    {
          try{
         p= DBDriver.getPrepare("select * from course where courseid=?");
         p.setString(1, courseid);
         rs=p.executeQuery();
         AO.Course course=new AO.Course();
         if(rs.next())
         {
        course.setCourseid(rs.getString("courseid"));
        course.setCourseName(rs.getString("coursename"));
        course.setSdate(rs.getString("startdate"));
        course.setEdate(rs.getString("enddate"));
        course.setFee(rs.getInt("fee"));
        course.setNoofFaculty(rs.getInt("nooffaculty"));
        course.setNoofExams(rs.getInt("noofexams"));

           }
         return course;
}catch(Exception e){return null;}
    

    }
    
    
    public static String[] getCourseIds()
{
    try
    {
     p=DBDriver.getPrepare("select courseid from course order by startdate desc");
     pstmt=DBDriver.getPrepare("select 1 from course");
    
    
        rs=p.executeQuery();
        int i=0,j=0;
        
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] courseIds=new String[j];
         while(rs.next())
        {
            courseIds[i++]=rs.getString("courseid");
         }
        return courseIds;
    }catch(Exception e){return null ;}
    
}

public static int remainingDays(String courseid)
{
    try
    {
     p=DBDriver.getPrepare("select DATEDIFF(enddate,now()) from course where courseid=? ");
     p.setString(1, courseid);
     rs=p.executeQuery();
     if(rs.next())
         return rs.getInt(1);
     return 0;
    }
    catch(Exception e){e.printStackTrace();return 0;}
    
}
  
public static int startedDays(String courseid)
{
    try
    {
     p=DBDriver.getPrepare("select DATEDIFF(now(),startdate) from course where courseid=? ");
     p.setString(1, courseid);
     rs=p.executeQuery();
    if(rs.next())
              return rs.getInt(1);
    return 0;
    }catch(Exception e){e.printStackTrace();return 0;}
}

public static String[] getNewCourseIds()
{
    try
    {
     p=DBDriver.getPrepare("select courseid from course where DATEDIFF(now(),startdate)<=0 order by startdate desc");
     pstmt=DBDriver.getPrepare("select 1 from course where DATEDIFF(now(),startdate)<=0");
    
        rs=p.executeQuery();
        int i=0,j=0;
        
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] courseIds=new String[j];
         while(rs.next())
        {
            courseIds[i++]=rs.getString("courseid");
         }
        return courseIds;
    }catch(Exception e){return null ;}
    
}

    
    
    public static String[] getUserCourseIds(String userid,boolean isadded)
    {
    try
      {
        p=DBDriver.getPrepare("select courseid from usercourselist where userid=? and isadded=?");
        p.setString(1, userid);
        p.setBoolean(2, isadded);
        rs=p.executeQuery();
        int i=0,j=0;
        pstmt=DBDriver.getPrepare("select 1 from usercourselist where userid=? and isadded=?");
        pstmt.setString(1, userid);
        pstmt.setBoolean(2, isadded);
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] courseIds=new String[j];
        while(rs.next())
        {
            courseIds[i++]=rs.getString("courseid");
        }
        return courseIds;
        
      }catch(Exception e){return null;}
    }


   public static String[] getCourseUserIds(String courseid)
    {
    try
    {
        p=DBDriver.getPrepare("select userid from usercourselist where courseid=?");
        p.setString(1, courseid);
        rs=p.executeQuery();
        int i=0,j=0;
        pstmt=DBDriver.getPrepare("select 1 from usercourselist where courseid=?");
        pstmt.setString(1, courseid);
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] userIds=new String[j];
        while(rs.next())
        {
            userIds[i++]=rs.getString("userid");
        }
        return userIds;
        
    }catch(Exception e){return null;}
}
   
   
   public static String[] getFaculty(String courseid,boolean addstatus)
    {
    try
    {
        p=DBDriver.getPrepare("select usercourselist.userid from usercourselist inner join faculty where usercourselist.courseid=? and usercourselist.userid=faculty.userid and usercourselist.isadded=?");
        p.setString(1, courseid);
        p.setBoolean(2, addstatus);
        rs=p.executeQuery();
        int i=0,j=0;
        pstmt=DBDriver.getPrepare("select 1 from usercourselist inner join faculty where usercourselist.courseid=? and usercourselist.userid=faculty.userid and usercourselist.isadded=?");
        pstmt.setString(1, courseid);
        pstmt.setBoolean(2, addstatus);
        ResultSet r=pstmt.executeQuery();
        
        while(r.next()) j++;
        
        String[] userIds=new String[j];
        while(rs.next())
        {
            userIds[i++]=rs.getString("userid");
        }
        return userIds;
        
    }catch(Exception e){return null;}
}
   
      
 public static boolean addCourse(String userid,String courseid)
 {
     try
    {
        
        
        p=DBDriver.getPrepare("insert into  usercourselist values(?,?,?)");
        p.setString(1, userid);
        p.setString(2, courseid);
        p.setBoolean(3, false);
        int i=p.executeUpdate();
        if(i==1) return true;
        else return false;
        
    }catch(Exception e){return false;}
 }
 public static boolean activateCourse(String userid,String courseid)
 {
       try
    {
        
        
        p=DBDriver.getPrepare("update usercourselist set isadded=1 where userid=? and courseid=?");
        p.setString(1, userid);
        p.setString(2, courseid);
        
        int i=p.executeUpdate();
        if(i==1) return true;
        else return false;
        
    }catch(Exception e){return false;}
 }
 
 public static boolean addRemoveFaculty(String userid, String courseid, boolean addstatus)
 {
     if(getRequiredNoofFaculty(courseid)==0&&addstatus)
         return false;
     try
    {
        p=DBDriver.getPrepare("update usercourselist set isadded=? where userid=? and courseid=?");
        p.setBoolean(1, addstatus);
        p.setString(2, userid);
        p.setString(3, courseid);
        
        int i=p.executeUpdate();
        if(i==1) return true;
        else return false;
        
    }catch(Exception e){return false;}
     
 }
 
 public static int getNoofFacultyAdded(String courseid)
 {
      try
    {
        
        
        p=DBDriver.getPrepare("select count(1) from usercourselist inner join faculty where usercourselist.courseid=? and usercourselist.userid=faculty.userid and usercourselist.isadded=1");
        p.setString(1, courseid);
        
        rs=p.executeQuery();
        if(rs.next())
            return rs.getInt(1);
        return 0;
        
    }catch(Exception e){return 0;}
     
 
 }
 public static int getRequiredNoofFaculty(String courseid)
 {
     int total=helper.course.getCourseBean(courseid).getNoofFaculty();
     int added=getNoofFacultyAdded(courseid);
      return total-added;
   
 }
 
 public static String subscribeStatus(String userid,String courseid)
 {
        try
    {
        String status=null;
        
        p=DBDriver.getPrepare("select isadded from usercourselist where userid=? and courseid=?");
        p.setString(1, userid);
        p.setString(2, courseid);
        rs=p.executeQuery();
        if(rs.next())
        {
        status="pending";
        if(rs.getBoolean("isadded")) status="added";
        }
        else      
        status="notadded";
        return status;
        
    }catch(Exception e){return null;}
     
 }
 
 
 
 public static void main(String[] a)
 {
      System.out.println(getNewCourseIds()[0]);
     
 }
}
